package cn.edu.ctbu.web.mapper;

import cn.edu.ctbu.web.dao.AdminGrade;
import cn.edu.ctbu.web.dao.StudentGrade;
import cn.edu.ctbu.web.dao.TeacherGrade;
import org.apache.ibatis.annotations.*;
import org.springframework.data.domain.Pageable;

import java.util.List;

@Mapper
public interface GradeMapper {
    @Select("SELECT \n" +
            "    c.no AS no,\n" +
            "    c.name AS name,\n" +
            "    t.name AS teacher,\n" +
            "\t\tg.grade AS grade,\n" +
            "    c.credit\n" +
            "FROM \n" +
            "    tb_grade g\n" +
            "JOIN \n" +
            "    tb_student s ON g.sno = s.no\n" +
            "JOIN \n" +
            "    tb_course c ON g.cno = c.no\n" +
            "JOIN \n" +
            "    tb_teacher t ON g.tno = t.no\n" +
            "WHERE \n" +
            "    s.no = #{no} \n" +
            "ORDER BY \n" +
            "    c.no;")
    // 使用学号查询课程成绩信息
    List<StudentGrade> findGradeByNoStudent(@Param("no") String no, Pageable pageable);

    @Select("SELECT COUNT(*) FROM tb_grade g " +
            "JOIN tb_student s ON g.sno = s.no " +
            "WHERE s.no = #{no}")
    // 使用学号查询课程成绩信息（计数）
    long countGradesByNoStudent(@Param("no") String no);


    @Select("SELECT \n" +
            "    c.no AS course_no,\n" +
            "    c.name AS course_name,\n" +
            "    s.no AS student_no,\n" +
            "    s.name AS student_name,\n" +
            "    g.grade AS course_grade\n" +
            "FROM \n" +
            "    tb_grade g\n" +
            "JOIN \n" +
            "    tb_teacher t ON g.tno = t.no\n" +
            "JOIN \n" +
            "    tb_course c ON g.cno = c.no\n" +
            "JOIN \n" +
            "    tb_student s ON g.sno = s.no\n" +
            "WHERE \n" +
            "    t.no = #{no}  \n" +
            "ORDER BY \n" +
            "    c.no, s.no;")
    // 使用教师编号查询授课课程学生成绩信息
    List<TeacherGrade> findGradeByNoTeacher(@Param("no") String no, Pageable pageable);

    @Select("SELECT COUNT(*) AS record_count\n" +
            "FROM \n" +
            "    tb_grade g\n" +
            "JOIN \n" +
            "    tb_teacher t ON g.tno = t.no\n" +
            "JOIN \n" +
            "    tb_course c ON g.cno = c.no\n" +
            "JOIN \n" +
            "    tb_student s ON g.sno = s.no\n" +
            "WHERE \n" +
            "    t.no = #{no}; ")
    // 使用教师编号查询授课课程学生成绩信息（计数）
    Long countGradeByNoTeacher(@Param("no") String no);

    @Select("SELECT \n" +
            "    g.id AS grade_id,\n" +
            "    s.no AS student_no,\n" +
            "    s.name AS student_name,\n" +
            "    c.no AS course_no,\n" +
            "    c.name AS course_name,\n" +
            "    c.credit AS course_credit,\n" +
            "    g.grade AS course_grade\n" +
            "FROM \n" +
            "    tb_grade g\n" +
            "JOIN \n" +
            "    tb_student s ON g.sno = s.no\n" +
            "JOIN \n" +
            "    tb_course c ON g.cno = c.no\n" +
            "ORDER BY \n" +
            "    g.id;")
    List<AdminGrade> findAllGrade(Pageable pageable);

    @Select("SELECT \n" +
            "    COUNT(*) AS total_grades\n" +
            "FROM \n" +
            "    tb_grade g\n")
    Long countAllGrades();


    /**
     * Teacher 增、删、改、查
     * {操作名}{操作对象}{使用层}
     **/

    @Insert("INSERT INTO tb_grade (sno, cno, tno, credit, grade)\n" +
            "SELECT \n" +
            "    #{sno} AS sno,\n" +
            "    c.no AS cno,\n" +
            "    c.tno AS tno,\n" +
            "    c.credit,\n" +
            "    #{grade} AS grade\n" +
            "FROM \n" +
            "    tb_course c\n" +
            "WHERE \n" +
            "    c.no = #{cno}\n" +
            "AND NOT EXISTS (\n" +
            "    SELECT 1 \n" +
            "    FROM tb_grade g \n" +
            "    WHERE g.sno = #{sno} AND g.cno = #{cno}\n" +
            ");")
    // 增添学生成绩信息
    void insertGradeTeacher(@Param("cno") String cno, @Param("sno") String sno, @Param("grade") int grade);

    @Delete("DELETE \n" +
            "FROM tb_grade\n" +
            "WHERE cno=#{cno} AND sno=#{sno}")
    // 删除学生成绩信息
    void deleteGradeTeacher(@Param("cno") String cno, @Param("sno") String sno);

    @Update("UPDATE tb_grade\n" +
            "SET grade = #{grade}  \n" +
            "WHERE cno = #{cno} \n" +
            "AND sno = #{sno};  ")
    // 更新学生成绩信息
    void updateGradeTeacher(@Param("cno") String cno, @Param("sno") String sno, @Param("grade") int grade);

    @Select("SELECT \n" +
            "    c.no AS course_no,\n" +
            "    c.name AS course_name,\n" +
            "    s.no AS student_no,\n" +
            "    s.name AS student_name,\n" +
            "    g.grade AS course_grade\n" +
            "FROM \n" +
            "    tb_grade g\n" +
            "JOIN \n" +
            "    tb_course c ON g.cno = c.no\n" +
            "JOIN \n" +
            "    tb_student s ON g.sno = s.no\n" +
            "WHERE \n" +
            "    c.no = #{cno} \n" +
            "ORDER BY \n" +
            "    s.no;")
    // 查询学生成绩信息
    List<TeacherGrade> searchGradeTeacher(@Param("cno") String cno, Pageable pageable);

    @Select("SELECT \n" +
            "    COUNT(*) AS total_student_grades\n" +
            "FROM \n" +
            "    tb_grade g\n" +
            "JOIN \n" +
            "    tb_course c ON g.cno = c.no\n" +
            "JOIN \n" +
            "    tb_student s ON g.sno = s.no\n" +
            "WHERE \n" +
            "    c.no = #{no}; ")
    // 查询学生成绩信息（计数）
    Long countGradeTeacher(@Param("no") String no);


}
